<%@Page Language="VB"%>

<%@Import Namespace="System.Data" %>
<%@Import Namespace="System.Data.OleDb" %>

<%@ Register TagPrefix="wrox" TagName="connect" Src="..\global\connect-strings.ascx" %>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html><head>
<title>Sorting and Filtering in a DataTable Object</title>
<!-- #include file="..\global\style.inc" -->
</head>
<body bgcolor="#ffffff">
<span class="heading">Sorting and Filtering in a DataTable Object</span><hr />
<!--------------------------------------------------------------------------->

<%'-- insert connection string script --%>
<wrox:connect id="ctlConnectStrings" runat="server"/>

<div>Connection string: <b><span id="outConnect" runat="server"></span></b></div>
<div>SELECT command: <b><span id="outSelect" runat="server"></span></b></div>
<div id="outError" runat="server">&nbsp;</div>

<div id="outMessage" runat="server"></div>

<form runat="server">
Sort records: <input type="submit" id="cmdTitle" value="By Title" runat="server" /> &nbsp;
<input type="submit" id="cmdISBN" value="By ISBN" runat="server" /> &nbsp;
<input type="submit" id="cmdDate" value="By Date" runat="server" /><p />
Search within titles: <input type="text" id="txtFind" size="20" runat="server" />
<input type="submit" id="cmdFind" value="Find" runat="server" />
</form>

<div id="outResult" runat="server"></div>

<script language="vb" runat="server">

Sub Page_Load()

   'get connection string from ..\global\connect-strings.ascx user control
   Dim strConnect = ctlConnectStrings.OLEDBConnectionString
   outConnect.innerText = strConnect 'and display it

   'specify the SELECT statement to extract the data
   Dim strSelect As String
   strSelect = "SELECT * FROM BookList WHERE ISBN LIKE '1861003%'"
   outSelect.innerText = strSelect   'and display it

   'declare a variable to hold a DataSet object
   'note that we have to create it outside the Try..Catch block
   'as this is a separate block and so is a different scope
   Dim objDataSet As New DataSet

   Try

      'create a new Connection object using the connection string
      Dim objConnect As New OleDbConnection(strConnect)

      'create a new DataAdapter using the connection object and select statement
      Dim objDataAdapter As New OleDbDataAdapter(strSelect, objConnect)

      'fill the dataset with data from the DataAdapter object
      objDataAdapter.Fill(objDataSet, "Books")

   Catch objError As Exception

      'display error details
      outError.innerHTML = "<b>* Error while accessing data</b>.<br />" _
          & objError.Message & "<br />" & objError.Source
      Exit Sub  ' and stop execution

   End Try

   'create the Sorting expression
   Dim strSortString As String = ""
   If Len(Request.Form("cmdTitle")) > 0 Then strSortString = "Title"
   If Len(Request.Form("cmdISBN")) > 0 Then strSortString = "ISBN"
   If Len(Request.Form("cmdDate")) > 0 Then strSortString = "PublicationDate DESC, Title"

   'or create the Filter expression
   Dim strFilterExpr As String = ""
   If Len(Request.Form("cmdFind")) > 0 Then
      strFilterExpr = "Title LIKE '*" & txtFind.Value & "*'"
   End If

   'display the parameters we're using
   outMessage.innerHTML = "Called <b>DataTable.Select</b>(""<b>" _
      & strFilterExpr & "</b>"", ""<b>" & strSortString & "</b>"")"

   'get a reference to the "Books" DataTable object
   Dim objTable As DataTable = objDataSet.Tables("Books")

   'create an array to hold the results then call the Select method
   Dim objResults() As DataRow
   objResults = objTable.Select(strFilterExpr, strSortString)

   'the result is an array of DataRow objects not a DataTable object
   'so we have to iterate through to get the row contents
   Dim objRow As DataRow
   Dim strResult As String = "<table>"
   For Each objRow In objResults
      strResult += "<tr><td>" & objRow(0) & "</td><td>&nbsp; " & objRow(1) _
                & "</td><td>&nbsp; " & objRow(2) & "</td></tr>"
   Next
   strResult += "</table>"

   outResult.InnerHtml = strResult   'and display the results

End Sub
</script>

<!--------------------------------------------------------------------------->
<!-- #include file="..\global\foot.inc" -->
</body>
</html>
